package com.mall.manager.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

import org.apache.commons.collections.MapUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import com.mall.common.dao.MysqlDBManager;
import com.mall.untils.DateUtils;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

/**
 * 八大能力更新/插入rds
 * @author tgy
 *
 */
@Service
public class AbilityRdsService {

	public static Logger logger = Logger.getLogger(AbilityRdsService.class);
	
	/**
	 * 八大能力更新插入rds
	 * @param name 省、市、区
	 * @param rex 省市区标示符：f_1,f_2...;h_1,h_2...,q_1,q_2...
	 * @param score
	 * @param map
	 */
	@SuppressWarnings("resource")
	public static void insertBatch(String name,String rex,Integer score,Map<String,Object> map){
		Connection conn = MysqlDBManager.getConnection();
		PreparedStatement ps=null;
		ResultSet res=null;
		try{
			//查询是否存在当前记录，若存在则更新记录，否则插入新的记录
			String code = MapUtils.getString(map, name);
			String sql = "select * from t_ability_statistics where area_id='"+code+"'";
			ps = (PreparedStatement) conn.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()){
				if(rex.contains("_1")){  //总分
					sql = "update t_ability_statistics set total_avg=?,update_time=? where area_id=?";
				}else if(rex.contains("_2")){  //社交能力
					sql = "update t_ability_statistics set social_avg=?,update_time=? where area_id=?";
				}else if(rex.contains("_3")){  //艺术能力
					sql = "update t_ability_statistics set art_avg=?,update_time=? where area_id=?";
				}else if(rex.contains("_4")){  //语言能力
					sql = "update t_ability_statistics set language_avg=?,update_time=? where area_id=?";
				}else if(rex.contains("_5")){  //认知能力
					sql = "update t_ability_statistics set cognition_avg=?,update_time=? where area_id=?";
				}else if(rex.contains("_6")){  //自理能力
					sql = "update t_ability_statistics set self_avg=?,update_time=? where area_id=?";
				}else if(rex.contains("_7")){  //运动能力
					sql = "update t_ability_statistics set sport_avg=?,update_time=? where area_id=?";
				}else if(rex.contains("_8")){  //精细能力
					sql = "update t_ability_statistics set perfect_avg=?,update_time=? where area_id=?";
				}else if(rex.contains("_9")){  //数学能力
					sql = "update t_ability_statistics set math_avg=?,update_time=? where area_id=?";
				}else{
					sql = null;
				}
				if(sql!=null){
					ps = (PreparedStatement) conn.prepareStatement(sql);
					ps.setDouble(1, Double.parseDouble(score.toString()));
					ps.setTimestamp(2, DateUtils.getTimestamp());
					ps.setString(3, code);
					ps.executeUpdate();
				}
			}else{
				if(rex.contains("_1")){  //总分
					sql = "insert into t_ability_statistics(area_id,total_avg,area_name,create_time,update_time) values(?,?,?,?,?)";
				}else if(rex.contains("_2")){  //社交能力
					sql = "insert into t_ability_statistics(area_id,social_avg,area_name,create_time,update_time) values(?,?,?,?,?)";
				}else if(rex.contains("_3")){  //艺术能力
					sql = "insert into t_ability_statistics(area_id,art_avg,area_name,create_time,update_time) values(?,?,?,?,?)";
				}else if(rex.contains("_4")){  //语言能力
					sql = "insert into t_ability_statistics(area_id,language_avg,area_name,create_time,update_time) values(?,?,?,?,?)";
				}else if(rex.contains("_5")){  //认知能力
					sql = "insert into t_ability_statistics(area_id,cognition_avg,area_name,create_time,update_time) values(?,?,?,?,?)";
				}else if(rex.contains("_6")){  //自理能力
					sql = "insert into t_ability_statistics(area_id,self_avg,area_name,create_time,update_time) values(?,?,?,?,?)";
				}else if(rex.contains("_7")){  //运动能力
					sql = "insert into t_ability_statistics(area_id,sport_avg,area_name,create_time,update_time) values(?,?,?,?,?)";
				}else if(rex.contains("_8")){  //精细能力
					sql = "insert into t_ability_statistics(area_id,perfect_avg,area_name,create_time,update_time) values(?,?,?,?,?)";
				}else if(rex.contains("_9")){  //数学能力
					sql = "insert into t_ability_statistics(area_id,math_avg,area_name,create_time,update_time) values(?,?,?,?,?)";
				}else{
					sql = null;
				}
				if(sql!=null){
					ps = (PreparedStatement) conn.prepareStatement(sql);
					ps.setString(1, code);
					ps.setDouble(2, Double.parseDouble(score.toString()));
					ps.setString(3, name);
					ps.setTimestamp(4, DateUtils.getTimestamp());
					ps.setTimestamp(5, DateUtils.getTimestamp());
					ps.execute();
				}
			}
			conn.commit();
		}catch(Exception e){
			logger.info("更新或插入八大能力出现异常："+e.getMessage());
		}finally{
			try {
				conn.close();
				ps.close();
				res.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}
